package com.amazon.service.impl;

import com.amazon.model.DaySaleModel;
import com.amazon.model.FlowModel;
import com.amazon.service.FlowingService;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Locale;

@Service
public class FlowingServiceImpl implements FlowingService {
    @Override
    public FlowModel getFlowingWater(Long startTime, Long endTime) {
        FlowModel flowModel = new FlowModel();
        List<DaySaleModel> daySaleModelList = new ArrayList<>();
        InputStream inputStream = null;
        Workbook workbook = null;
        try {
            File file = new File("D:\\data\\amazon_data.xlsx");
            inputStream = new FileInputStream(file);
            workbook = new XSSFWorkbook(inputStream);
            Sheet sheet = workbook.getSheetAt(1);
            // 跳过第一行
            int rowOffset = 1;
            FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();

            Integer unitsOrderedTotal = 0;
            Integer totalOrderItems = 0;
            Double lastUnitsOrderedTotal = 0.0;
            Double LastOrderedProductSalesTotal = 0.0;
            Double OrderedProductSalesTotal = 0.0;
            Double salesOrderItemTotal = 0.0;
            for (int rowNum = sheet.getFirstRowNum() + rowOffset; rowNum <= sheet.getLastRowNum();
                 rowNum++) {
                Row row = sheet.getRow(rowNum);
                if (row == null) {
                    continue;
                }
                DaySaleModel daySaleModel = new DaySaleModel();
                // 处理每一行的数据
                for (int colNum = row.getFirstCellNum(); colNum < row.getLastCellNum(); colNum++) {
                    Cell cell = row.getCell(colNum);
                    if (cell == null) {
                        continue;
                    }
                    if(colNum == 0){
                        SimpleDateFormat sdf = new SimpleDateFormat("dd-MMM-yyyy", Locale.CHINESE);
                        Date date = sdf.parse(cell.toString());
                        long timestamp = date.getTime();
                        if(timestamp >= startTime && timestamp <= endTime ){
                            daySaleModel.setDate(timestamp);
                            continue;
                        } else {
                            daySaleModel = null;
                            break;
                        }

                    }
                    CellValue cellValue = evaluator.evaluate(cell);
                    if (cellValue ==null){
                        continue;
                    }
                    // 统计每一个单元格数据
                    double value = cellValue.getNumberValue();
                    if(colNum == 1){
                        daySaleModel.setTotalOrderItems((int)value);
                        totalOrderItems += (int)value;
                    }
                    if(colNum == 2){
                        daySaleModel.setUnitsOrdered((int)value);
                        unitsOrderedTotal += (int)value;
                    }
                    if(colNum == 3){
                        daySaleModel.setOrderedProductSales(value);
                        OrderedProductSalesTotal += value;
                    }
                    if(colNum == 4){
                        daySaleModel.setUnitsOrderItem(value);
                    }
                    if(colNum == 5){
                        daySaleModel.setSalesOrderItem(value);
                        salesOrderItemTotal += value;
                    }
                    if(colNum == 6){
                        daySaleModel.setLastUnitsOrdered(value);
                        lastUnitsOrderedTotal += value;
                    }
                    if(colNum == 7){
                        daySaleModel.setLastOrderedProductSales(value);
                        LastOrderedProductSalesTotal += value;
                    }
                    if(colNum == 8){
                        daySaleModel.setLastTotalInterval(value);
                    }
                    if(colNum == 9){
                        daySaleModel.setLastSales(value);
                    }
                    if(colNum == 10){
                        daySaleModel.setLastRate(value);
                    }
                }
                if (daySaleModel != null) {
                    daySaleModelList.add(daySaleModel);
                }
            }
            flowModel.setDatas(daySaleModelList);
            flowModel.setUnitsOrdered(unitsOrderedTotal);
            flowModel.setTotalOrderItems(totalOrderItems);
            flowModel.setLastUnitsOrdered(lastUnitsOrderedTotal.intValue());
            flowModel.setLastOrderedProductSales(LastOrderedProductSalesTotal);
            flowModel.setOrderedProductSales(OrderedProductSalesTotal);
            flowModel.setSalesOrderItem(OrderedProductSalesTotal / totalOrderItems);
            BigDecimal a = new BigDecimal(unitsOrderedTotal);
            BigDecimal b = new BigDecimal(totalOrderItems);
            // 设置四舍五入，保留两位小数
            BigDecimal result = a.divide(b, 2, RoundingMode.HALF_UP);
            flowModel.setUnitsOrderItem(result.doubleValue());
            flowModel.setSelectedDateRange(1);
            flowModel.setSnapshotTime(new Date());
            flowModel.setSelectedDateRangeOneYearAgo(0);
            Sheet sheet0 = workbook.getSheetAt(0);
            Row row = sheet0.getRow(1);
            String storeName = "";
            String webSite = "";
            if(row != null){
                Cell cell = row.getCell(0);
                storeName = cell.getStringCellValue();
                Cell cell1 = row.getCell(1);
                webSite = cell1.getStringCellValue();
            }
            flowModel.setStore(storeName);
            flowModel.setWebsite(webSite);
        } catch (Exception e){
            e.printStackTrace();
        } finally {
            if(workbook != null){
                try {
                    workbook.close();
                } catch (Exception e){
                    e.printStackTrace();
                }

            }
            if(inputStream != null){
                try {
                    inputStream.close();
                } catch (Exception e){
                    e.printStackTrace();
                }

            }
        }
        return flowModel;
    }

    public static void main(String[] args) throws Exception{
        FlowingServiceImpl service = new FlowingServiceImpl();
        //service.getFlowingWater(1704038400000L,1704384000000L);

        SimpleDateFormat sdf = new SimpleDateFormat("dd-MMM-yyyy", Locale.CHINESE);
        Date date = sdf.parse("03-一月-2024");
        Date date1 = sdf.parse("05-一月-2024");
        long timestamp = date.getTime();
        long timestamp1 = date1.getTime();
        System.out.println(timestamp);
        System.out.println(timestamp1);
    }
}
